package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MMovement;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import org.utils.AnalogConvertor_RU;
import extend.org.compiere.utils.Util;

public class MM_Form_Z5 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	private int p_Record_ID, p_Delivery_ID, p_ReleaseAllowed_ID, p_Released_ID, p_Checked_ID, p_Received_ID;
	
	Date p_DateDoc;
	
	String ResultPath, p_DocumentBase, p_DocumentBaseNo;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	p_Record_ID = pi.getRecord_ID();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("M_Movement_ID"))
			p_Record_ID = para[i].getParameterAsInt();
		else if (name.equals("Delivery_ID"))
			p_Delivery_ID = para[i].getParameterAsInt();
		else if (name.equals("ReleaseAllowed_ID"))
			p_ReleaseAllowed_ID = para[i].getParameterAsInt();
		else if (name.equals("Released_ID"))
			p_Released_ID = para[i].getParameterAsInt();
		else if (name.equals("Checked_ID"))
			p_Checked_ID = para[i].getParameterAsInt();
		else if (name.equals("Received_ID"))
			p_Received_ID = para[i].getParameterAsInt();
		else if (name.equals("DocumentBase"))
			p_DocumentBase = (String) para[i].getParameter();
		else if (name.equals("DocumentBaseNo"))
			p_DocumentBaseNo = (String) para[i].getParameter();
		else if (name.equals("DateDoc"))
			p_DateDoc = (Date) para[i].getParameter();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Z5_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 9) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "Shablon tabylmady"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		mergeStyle.setFont(wb.getFontAt(sheet.getRow(9).getCell(0).getCellStyle().getFontIndex()));
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(9).getCell(21).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(49).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		//Document Header
		MMovement mov = new MMovement(m_ctx, p_Record_ID, get_TrxName());
		
		sheet.getRow(19).getCell(0).setCellValue(mov.getDocumentNo());
		sheet.getRow(19).getCell(5).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(mov.getMovementDate())));
		sheet.getRow(19).getCell(35).setCellValue(p_DocumentBase);
		sheet.getRow(19).getCell(44).setCellValue(p_DocumentBaseNo);
		sheet.getRow(19).getCell(49).setCellValue(( new SimpleDateFormat("dd.MM.yyyy" ).format(p_DateDoc)));
		sheet.getRow(19).getCell(55).setCellValue((new MBPartner(m_ctx, p_Delivery_ID, get_TrxName()).getName()));
		
		//Document Footer
		String sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_ReleaseAllowed_ID;
		int id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(28).getCell(8).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(28).getCell(23).setCellValue((new MBPartner(m_ctx, p_ReleaseAllowed_ID, get_TrxName()).getName()));
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_Checked_ID;
		id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(28).getCell(40).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(28).getCell(57).setCellValue((new MBPartner(m_ctx, p_Checked_ID, get_TrxName()).getName()));
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_Released_ID;
		id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(31).getCell(5).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(31).getCell(23).setCellValue((new MBPartner(m_ctx, p_Released_ID, get_TrxName()).getName()));
		sqlfooter = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_Received_ID;
		id = DB.getSQLValue(get_TrxName(), sqlfooter);
		sheet.getRow(31).getCell(40).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(31).getCell(57).setCellValue((new MBPartner(m_ctx, p_Received_ID, get_TrxName()).getName()));
		
		//Positions
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT 	mhead.documentno " + 
								", mhead.movementdate " + 
								", w.name || '-' || l.value AS locFrom " + 
								", wto.name || '-' || lto.value AS locTo " + 
								", p.name AS product " + 
								", p.value AS product_number " + 
								", uom.name AS uom " + 
								", mline.movementqty AS creditqty " + 
								", round(fa.amtacctcr / mline.movementqty, 2) AS colprice " + 
								", fa.amtacctcr AS creditamount " + 
							"FROM fact_acct fa " + 
							"JOIN ad_table t ON t.ad_table_id = fa.ad_table_id " + 
							"JOIN m_movement mhead ON fa.record_id = mhead.m_movement_id " + 
							"JOIN m_movementline mline ON fa.record_id = mline.m_movement_id AND fa.line_id = mline.m_movementline_id " + 
							"JOIN m_locator l ON l.m_locator_id = mline.m_locator_id " + 
							"JOIN m_locator lto ON lto.m_locator_id = mline.m_locatorto_id " + 
							"JOIN m_warehouse w ON w.m_warehouse_id = l.m_warehouse_id " + 
							"JOIN m_warehouse wto ON wto.m_warehouse_id = lto.m_warehouse_id " + 
							"JOIN m_product p ON p.m_product_id = mline.m_product_id " + 
							"JOIN c_uom_trl uom ON uom.c_uom_id = p.c_uom_id " + 
							"WHERE fa.ad_table_id = 323 " + 
								"AND mhead.docstatus <> 'VO'::bpchar " +  
								"AND mhead.docstatus <> 'RE'::bpchar  " + 
								"AND fa.counter < fa.reference " + 
								"AND mhead.m_movement_id = " + p_Record_ID;
		
		int row = 25;
		int no = 0, totalQty = 0;;
		double totalAmt = 0;
		
		try {
			ps = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = ps.executeQuery();
			
			while (rs.next()) {
				if (rs.isFirst()) {
					sheet.getRow(19).getCell(11).setCellValue(rs.getString("locFrom"));
					sheet.getRow(19).getCell(23).setCellValue(rs.getString("locTo"));
				}
				
				//Shifting and Merging
				sheet.shiftRows(row, row + 10, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 4, 22));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 23, 34));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 35, 39));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 45));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 46, 51));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 52, 57));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 58, 64));
				
				R = sheet.createRow(row);
				for(int i = 0; i <= 64; i++){
					C = R.createCell(i);
					C.setCellStyle(mergeStyle);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(++no);
				sheet.getRow(row).getCell(4).setCellValue(rs.getString("product"));
				sheet.getRow(row).getCell(23).setCellValue(rs.getString("product_number"));
				sheet.getRow(row).getCell(35).setCellValue(rs.getString("uom"));
				sheet.getRow(row).getCell(40).setCellValue(rs.getInt("creditqty"));
				sheet.getRow(row).getCell(46).setCellValue(rs.getInt("creditqty"));
				totalQty += rs.getInt("creditqty");
				sheet.getRow(row).getCell(52).setCellValue(rs.getDouble("colprice"));
				sheet.getRow(row).getCell(58).setCellValue(rs.getDouble("creditamount"));
				totalAmt += rs.getDouble("creditamount");
				
				row++;
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}

		sheet.getRow(row + 1).getCell(17).setCellValue(AnalogConvertor_RU.toString(totalQty));
		sheet.getRow(row + 1).getCell(43).setCellValue(AnalogConvertor_RU.toString(totalAmt));
		
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}
}